package org.jeecg.modules.nikeProject.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.util.EmailUtil;
import org.jeecg.modules.nikeProject.entity.SportsProjectStatData;
import org.jeecg.modules.nikeProject.entity.vo.UnSubmittedUserVO;
import org.jeecg.modules.nikeProject.mapper.SportsProjectMapper;
import org.jeecg.modules.nikeProject.mapper.SportsProjectStatDataMapper;
import org.jeecg.modules.nikeProject.service.ISportsProjectStatDataService;
import org.jeecg.modules.system.entity.SysDict;
import org.jeecg.modules.system.entity.SysUser;
import org.jeecg.modules.system.mapper.SysDictMapper;
import org.jeecg.modules.system.mapper.SysLogMapper;
import org.jeecg.modules.system.mapper.SysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description: 每日填报数据统计表
 * @Author: jeecg-boot
 * @Date:   2025-06-13
 * @Version: V1.0
 */
@Service
public class SportsProjectStatDataServiceImpl extends ServiceImpl<SportsProjectStatDataMapper, SportsProjectStatData> implements ISportsProjectStatDataService {

    @Autowired
    private SysLogMapper sysLogMapper;
    @Autowired
    private SysUserMapper sysUserMapper;
    @Autowired
    private SysDictMapper sysDictMapper;
    @Autowired
    private SportsProjectMapper sportsProjectMapper;
    @Autowired
    private SportsProjectStatDataMapper sportsProjectStatDataMapper;

    private final static String startTime = "2025-05-06 00:00:00";
    private final static String time = " 12:00:00";
    private final static String filePath = "data.xlsx";
    private final static String unsubmittedDataPath = "unsubmitted.xlsx";

    public SportsProjectStatData getProjectStatData(Integer year, String date) {
        LambdaQueryWrapper<SportsProjectStatData> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SportsProjectStatData::getDate, date);
        queryWrapper.eq(SportsProjectStatData::getYear, year);
        return sportsProjectStatDataMapper.selectOne(queryWrapper);
    }

    @Override
    public Result<?> getDayData(String date) {
        //年
        Calendar calendar  = Calendar.getInstance();
        Integer year = calendar.get(Calendar.YEAR);
        // 日期
        if (null == date || "".equals(date)) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            date = sdf.format(new Date());
        }
        SportsProjectStatData dayData = getProjectStatData(year, date);
        //为空则新增
        if (dayData == null) {
            dayData = new SportsProjectStatData();
            dayData.setYear(calendar.get(Calendar.YEAR));
            dayData.setDate(date);
            addDayData(dayData);
        }
        //每日数据统计写入excel
        List<SportsProjectStatData> list = this.getList();
        writeListToExcel(list);

        //每日未提交数据统计
        List<UnSubmittedUserVO> userList = getUnSubmittedData(date, year);
        writeUnSubmittedUserListToExcel(userList);

        List<DictModel> dictList = sysDictMapper.queryDictItemsByCode(CommonConstant.STAT_DATA_EMAIL_DICT_CODE);
        List<String> emailList = dictList.stream().map(DictModel::getValue).collect(Collectors.toList());
        String [] receiver = emailList.toArray(new String[0]);
//        String [] receiver = {"36345846@qq.com"};
        String subject = "活力校园数据统计";
        String content = "<p>活力校园，提报数据统计，请在附件中查看！！！<p>";
        try {
            //发送带附件的邮件
            EmailUtil.sendEmailAttachment(receiver, subject, content, filePath, unsubmittedDataPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Result.OK("success");
    }

    public List<SportsProjectStatData> getList() {
        LambdaQueryWrapper<SportsProjectStatData> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.orderByAsc(SportsProjectStatData::getDate);
        return sportsProjectStatDataMapper.selectList(lambdaQueryWrapper);
    }

    public List<UnSubmittedUserVO> getUnSubmittedData(String date, Integer year){
        String endDate = date + time;
        return sportsProjectMapper.getUnSubmittedData(startTime, endDate, year);
    }

    public static void writeUnSubmittedUserListToExcel(List<UnSubmittedUserVO> list) {
        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Unsubmitted Data");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        String[] headers = {"姓名", "手机号", "填报人角色类型"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 1);
            UnSubmittedUserVO statData = list.get(i);

            // 设置单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);

            // 写入数据
            Cell dateCell = row.createCell(0);
            dateCell.setCellValue(statData.getUsername());
            dateCell.setCellStyle(cellStyle);

            Cell pvCell = row.createCell(1);
            pvCell.setCellValue(statData.getPhone());
            pvCell.setCellStyle(cellStyle);

            Cell roleTypeCell = row.createCell(2);
            roleTypeCell.setCellValue(statData.getRoleTypeName());
            roleTypeCell.setCellStyle(cellStyle);
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 保存到文件
        try (FileOutputStream fileOut = new FileOutputStream(unsubmittedDataPath)) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    // 将List数据写入Excel文件
    public static void writeListToExcel(List<SportsProjectStatData> list) {
        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Person Data");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        String[] headers = {"统计日期", "申报页面点击量", "总用户数", "新注册人数", "登录次数", "开始填报人数", "成功提交人数", "教师提交人数", "教研员提交人数", "校长提交人数", "大使提交人数"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 1);
            SportsProjectStatData statData = list.get(i);

            // 设置单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);

            // 写入数据
            Cell dateCell = row.createCell(0);
            dateCell.setCellValue(statData.getDate());
            dateCell.setCellStyle(cellStyle);

            Cell pvCell = row.createCell(1);
            pvCell.setCellValue(statData.getSupplyPv());
            pvCell.setCellStyle(cellStyle);

            Cell uvCell = row.createCell(2);
            uvCell.setCellValue(statData.getUv());
            uvCell.setCellStyle(cellStyle);

            Cell registerCell = row.createCell(3);
            registerCell.setCellValue(statData.getRegisterCnt());
            registerCell.setCellStyle(cellStyle);

            Cell loginCell = row.createCell(4);
            loginCell.setCellValue(statData.getLoginCnt());
            loginCell.setCellStyle(cellStyle);

            Cell fillCell = row.createCell(5);
            fillCell.setCellValue(statData.getFillCnt());
            fillCell.setCellStyle(cellStyle);

            Cell submitCell = row.createCell(6);
            submitCell.setCellValue(statData.getSubmitCnt());
            submitCell.setCellStyle(cellStyle);

            Cell teCell = row.createCell(7);
            teCell.setCellValue(statData.getTeSubmitCnt());
            teCell.setCellStyle(cellStyle);

            Cell srCell = row.createCell(8);
            srCell.setCellValue(statData.getSrSubmitCnt());
            srCell.setCellStyle(cellStyle);

            Cell smCell = row.createCell(9);
            smCell.setCellValue(statData.getSmSubmitCnt());
            smCell.setCellStyle(cellStyle);

            Cell paCell = row.createCell(10);
            paCell.setCellValue(statData.getPaSubmitCnt());
            paCell.setCellStyle(cellStyle);
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 保存到文件
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public String getYesterday(){
        LocalDateTime yesterday = LocalDateTime.now().minusDays(1);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        return yesterday.format(formatter);
    }

    public void addDayData(SportsProjectStatData dayData){
        String endTime = dayData.getDate() + time;
        //申报页面点击量
        int supplyPV = sysLogMapper.getSupplyPv(startTime, endTime);
        dayData.setSupplyPv(supplyPV);

        //总用户数
        dayData.setUv(sysLogMapper.getUv(startTime, endTime));

        //新注册人数
        dayData.setRegisterCnt(sysUserMapper.getRegisterCnt(startTime, endTime));

        //登录次数
        dayData.setLoginCnt(sysLogMapper.getLoginCnt(startTime, endTime));
        //开始填报人数
        dayData.setFillCnt(sportsProjectMapper.getFillCnt(startTime, endTime));

        //教师提交人数
        dayData.setTeSubmitCnt(sportsProjectMapper.getSubmitCnt(startTime, endTime, CommonConstant.ROLE_TYPE_TE));

        //教研员提交人数
        dayData.setSrSubmitCnt(sportsProjectMapper.getSubmitCnt(startTime, endTime, CommonConstant.ROLE_TYPE_SR));

        //校长提交人数
        dayData.setSmSubmitCnt(sportsProjectMapper.getSubmitCnt(startTime, endTime, CommonConstant.ROLE_TYPE_SM));

        //大使提交人数
        dayData.setPaSubmitCnt(sportsProjectMapper.getSubmitCnt(startTime, endTime, CommonConstant.ROLE_TYPE_PA));

        //成功提交人数
        dayData.setSubmitCnt(sportsProjectMapper.getSubmitCnt(startTime, endTime, null));
        sportsProjectStatDataMapper.insert(dayData);
    }
}
